package com.bst.common.utils.sql;

import com.bst.common.constant.JobConstant;
import com.bst.common.utils.DateUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

public class EtlUtil {
    public static final String DT_DAY_BEGIN_KEY = "${DT_DAY_BEGIN}";//date类型的日期的开始
    public static final String DT_DAY_BEGIN_INT_KEY = "${DT_DAY_BEGIN_INT}";//date类型的日期的开始
    public static final String D_BEGIN_KEY = "${D_BEGIN}";//字符串类型的日期开始
    public static final String DT_DAY_END_KEY = "${DT_DAY_END}";//date类型的日期的开始
    public static final String DT_DAY_END_INT_KEY = "${DT_DAY_END_INT}";//date类型的日期的开始
    public static final String D_END_KEY = "${D_END}";//字符串类型的日期结束
    //public static final String SD_ORG="${"

    public static String getMaxNumSql(String sql,Integer pageSize,String dbType,String order) {
        order = StringUtils.isEmpty(order)?"":("order by "+order);
        if(JobConstant.DB_TYPE_MYSQL.equals(dbType)) {
            sql = "select *　from ("+sql+") aa "+order+" limit "+pageSize;
        } else if (JobConstant.DB_TYPE_SQLSERVER.equals(dbType)) {
            sql = "select top "+pageSize+" *　from ("+sql+") aa "+order;
        } else if (JobConstant.DB_TYPE_ORACLE.equals(dbType)) {
            sql = "select *　from ("+sql+") aa "+order+" where rownum< "+pageSize;
        }  else if (JobConstant.DB_TYPE_POSTGREY.equals(dbType)) {
            sql = "select *　from ("+sql+") aa "+order+" limit "+pageSize;
        }
        return sql;
    }
    /**
     * 填充参数
     *
     * @param start
     * @param end
     * @return
     */
    public static Map<String, String> getGlobalParam(Date start, Date end, String dbType) {
        Map<String, String> map = new LinkedHashMap<>();
        String beginDayStr = DateUtil.toDateStrByFormat(start, "yyyy-MM-dd HH:mm:ss");
        String endDayStr = DateUtil.toDateStrByFormat(end, "yyyy-MM-dd HH:mm:ss");
        String dBeginStr = DateUtil.toDateStrByFormat(start, "yyyyMMdd");
        String dEndStr = DateUtil.toDateStrByFormat(end, "yyyyMMdd");
        if (dbType.equals(JobConstant.DB_TYPE_MYSQL) || dbType.equals(JobConstant.DB_TYPE_POSTGREY)) {
            map.put(DT_DAY_BEGIN_KEY, "'" + beginDayStr + "'");
            map.put(DT_DAY_END_KEY, "'" + endDayStr + "'");
        } else if (dbType.equals(JobConstant.DB_TYPE_ORACLE)) {
            map.put(DT_DAY_BEGIN_KEY, "to_date ('" + dBeginStr + "','yyyymmdd')");
            map.put(DT_DAY_END_KEY, "to_date ('" + dEndStr + "','yyyymmdd')");
        } else if (dbType.equals(JobConstant.DB_TYPE_SQLSERVER)) {
            map.put(DT_DAY_BEGIN_KEY, "'" + beginDayStr + "'");
            map.put(DT_DAY_END_KEY, "'" + endDayStr + "'");
        }
        map.put(D_BEGIN_KEY, "'" + dBeginStr + "'");
        map.put(DT_DAY_BEGIN_INT_KEY, dBeginStr);
        map.put(D_END_KEY, "'" + dEndStr + "'");
        map.put(DT_DAY_END_INT_KEY, dEndStr);


        return map;
    }

    public static String buildSql(String sql, Date startTime, Date endTime, String dbType) {
        Map<String, String> map = EtlUtil.getGlobalParam(startTime, endTime, dbType);

        for (Map.Entry<String, String> entry : map.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            while (sql.indexOf(key) != -1)
                sql = sql.replace(key, value);
        }
        return sql;
    }

    private static final String VALUE_HOLDER = "?";

    public static String getInsertSQL(List<String> columns, String table) {
        List<String> columnHolders = new ArrayList<>(columns.size());
        List<String> valueHolders = new ArrayList<>(columns.size());

        for (String columnHolder : columns) {
            valueHolders.add(VALUE_HOLDER);
            columnHolders.add(columnHolder);
        }
        return new StringBuilder()
                .append("INSERT INTO ").append(table).append(" (").append(StringUtils.join(columnHolders, ","))
                .append(") VALUES(").append(StringUtils.join(valueHolders, ","))
                .append(")").toString();
    }
}
